/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBHandling;

import Entity.Item;
import java.sql.ResultSet;
import java.util.Vector;

/**
 *
 * @author HINSKY
 */
public class ItemHandling {
    private DBUtil du;

    public ItemHandling() {
        du = new DBUtil();
    }
    
    public Vector<Item> getItems() throws Exception {
        ResultSet rs = du.executeSQL("Select * from Items");   //get all items in database
        
        Vector<Item> items = new Vector<Item>();
        while (rs.next()) {
            Item i = new Item();
            i.setItemID(rs.getString("itemID"));
            i.setItemName(rs.getString("itemName"));
            i.setUnit(rs.getString("unit"));
            i.setStatus(rs.getInt("status"));
            i.setPrice(rs.getDouble("price"));
            i.setCateID(rs.getString("categoryID"));
            i.setReturnable(rs.getBoolean("returnable"));
            items.add(i);
        }
        du.closeConnection();
        return items;
    }
 
    public Vector<String> getItemNames(boolean notAvail) throws Exception{
        String sql = "Select itemName from Items";
        if(notAvail){
            sql+= " WHERE status < 10";
        }
        ResultSet rs = du.executeSQL(sql);   //get all items not available in database
        
        Vector<String> items = new Vector<String>();
        while (rs.next()) {
            items.add(rs.getString("itemName"));
        }
        du.closeConnection();
        return items;
    }
    
    public boolean insertItem(Item item) throws Exception {
        try {
            String sql = "{call sp_addItem" + "(?, ?, ?, ?, ?, ?, ?)}";
            Object[] objects = {item.getItemID(), item.getItemName(), item.getUnit(),item.getStatus(), item.getPrice(), item.getCateID(),item.isReturnable()? 1 : 0};
            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }
    
    public boolean updateItem(Item item) throws Exception {
        try {
            String sql = "{call sp_updateItem" + "(?, ?, ?, ?, ?, ?, ?)}";
            Object[] objects = {item.getItemID(), item.getItemName(), item.getUnit(),item.getStatus(), item.getPrice(), item.isReturnable()? 1 : 0, item.getCateID()};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }
    public boolean removeItem(String id)throws Exception {
        try {
            String sql = "{call sp_removeItem" + "(?)}";
            Object[] objects = {id};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }
    public boolean updateItemQuantity(String itemID, int quantity) throws Exception {
        try {
            String sql = "{call sp_updateItemQuantity" + "(?, ?)}";
            Object[] objects = {itemID, quantity};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }
}
